星尘

Flask Sanic 数据库操作对比

发表于 更新于 分类于

Sanic

sanic 既然是使用了 io 异步，那么我们就使用 aiomysql 和 sqlalchemy 来对数据进行处理

获取 engine

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
from aiomysql.sa import create_engine

DB_SETTING = {
    "user": "root",
    "db": "sanic_demo",
    "host": "127.0.0.1",
    "password": "123456",
    "autocommit": True
}


async def get_engine() -> object:
    engine = await create_engine(
        user=DB_SETTING.get("user"),
        db=DB_SETTING.get("db"),
        host=DB_SETTING.get("host"),
        password=DB_SETTING.get("password"),
        autocommit=DB_SETTING.get("autocommit")
    )
    return engine

创建model

创建一张叫做 sanic 的表，字段为 id 和 user_name。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
import sqlalchemy as sa
from sanic_part.model import get_engine

metadata = sa.MetaData()

tbl = sa.Table(
    'sanic',
    metadata,
    sa.Column('id', sa.Integer),
    sa.Column('user_name', sa.String(16))
)


async def insert_data(**data: dict) -> None:
    engine = await get_engine()
    async with engine.acquire() as conn:
        await conn.execute(tbl.insert().values(data))

新增数据

在 POST 中定义一组数据，每次调用一次就会新增一组数据。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
class SimpleAsyncView(HTTPMethodView):
    async def get(self, request):
        return text('I am async get method')

    async def post(self, request):
        data = {
            "id": "1",
            "user_name": "test"
        }
        await insert_data(**data)
        return text('I am async post method')

    async def put(self, request):
        return text('I am async put method')

测试

这里用压测来新增数据，其实是不规范的，不过这样也是测试的 aiomysql 的一种方式。

1
wrk -t4 -c10 -d10s -T10s --script=post.lua --latency http://127.0.0.1:8673/async
直接 Python 启动
1
2
3
4
5
6
7
8
9
10
11
12
13
14
192:wrk rex$ wrk -t4 -c10 -d10s -T10s --script=post.lua --latency http://127.0.0.1:8673/async
Running 10s test @ http://127.0.0.1:8673/async
  4 threads and 10 connections
  Thread Stats   Avg      Stdev     Max   +/- Stdev
    Latency    13.81ms    4.78ms  71.24ms   77.99%
    Req/Sec   146.06     12.73   190.00     61.25%
  Latency Distribution
     50%   13.10ms
     75%   15.87ms
     90%   19.13ms
     99%   28.23ms
  5832 requests in 10.02s, 803.04KB read
Requests/sec:    581.76
Transfer/sec:     80.11KB

Requests/sec 为：581.76

gunicorn 启动
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
192:wrk rex$ wrk -t4 -c10 -d10s -T10s --script=post.lua --latency http://127.0.0.1:8673/async
Running 10s test @ http://127.0.0.1:8673/async
  4 threads and 10 connections
  Thread Stats   Avg      Stdev     Max   +/- Stdev
    Latency    63.26ms   54.39ms 215.45ms   77.59%
    Req/Sec    34.98     33.90   120.00     74.66%
  Latency Distribution
     50%   26.45ms
     75%  111.19ms
     90%  149.55ms
     99%  187.75ms
  1325 requests in 10.04s, 10.95MB read
  Non-2xx or 3xx responses: 1325
Requests/sec:    132.01
Transfer/sec:      1.09MB

Flask

初始化连接

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
with app.app_context():
    engine = create_engine(
        "mysql+pymysql://root:123456@127.0.0.1:3306/sanic_demo",
        pool_pre_ping=True,
        encoding='utf-8',
        poolclass=NullPool,
        convert_unicode=True,
        echo=True
    )

session_factory = sessionmaker(bind=engine)


@contextmanager
def session_scope():
    session = session_factory()
    try:
        yield session
        session.commit()
    except Exception as e:
        print(e)
        session.rollback()
        raise
    finally:
        pass

创建 model

1
2
3
4
5
6
7
8
from flask_part import Base
from sqlalchemy import Column, String


class User(Base):
    __tablename__ = 'sanic'
    id = Column(String(10), nullable=True, primary_key=True)
    user_name = Column(String(10), nullable=True)

新增数据

1
2
3
4
5
6
7
8
9
10
11
@app.route("/save_data", methods=["POST"])
def post():
    data = {
        "id": "1",
        "user_name": "test"
    }
    with session_scope() as session:
        obj = User(**data)
        session.add(obj)
        session.commit()
    return "I am flask post method"

测试

直接 Python 启动
1
2
3
4
5
6
7
8
9
10
11
12
13
14
192:wrk rex$ wrk -t4 -c10 -d10s -T10s --script=post.lua --latency http://127.0.0.1:8673/save_data
Running 10s test @ http://127.0.0.1:8673/save_data
  4 threads and 10 connections
  Thread Stats   Avg      Stdev     Max   +/- Stdev
    Latency    25.36ms    4.72ms  50.67ms   71.32%
    Req/Sec    78.24      9.29   101.00     69.00%
  Latency Distribution
     50%   24.58ms
     75%   27.83ms
     90%   31.76ms
     99%   39.28ms
  3145 requests in 10.07s, 540.55KB read
Requests/sec:    312.37
Transfer/sec:     53.69KB
gunicorn 启动